<?php

/**
 * model_excel
 */

class Model_excel extends YOU {




	/** 数字转英文 **/
	public $letter_ini = array(
	"0"  => "A",
	"1"  => "B",
	"2"  => "C",
	"3"  => "D",
	"4"  => "E",
	"5"  => "F",
	"6"  => "G",
	"7"  => "H",
	"8"  => "I",
	"9"  => "J",
	"10" => "K",
	"11" => "L",
	"12" => "M",
	"13" => "N",
	"14" => "O",
	"15" => "P",
	"16" => "Q",
	"17" => "R",
	"18" => "S",
	"19" => "T",
	"20" => "U",
	"21" => "V",
	"22" => "W",
	"23" => "X",
	"24" => "Y",
	"25" => "Z",
	"26"  => "AA",
	"27"  => "AB",
	"28"  => "AC",
	"29"  => "AD",
	"30"  => "AE",
	"31"  => "AF",
	"32"  => "AG",
	"33"  => "AH",
	"34"  => "AI",
	"35"  => "AJ",
	"36" => "AK",
	"37" => "AL",
	"38" => "AM",
	"39" => "AN",
	"40" => "AO",
	"41" => "AP",
	"42" => "AQ",
	"43" => "AR",
	"44" => "AS",
	"45" => "AT",
	"46" => "AU",
	"47" => "AV",
	"48" => "AW",
	"49" => "AX",
	"50" => "AY",
	"51" => "AZ",
	"52"  => "BA",
	"53"  => "BB",
	"54"  => "BC",
	"55"  => "BD",
	"56"  => "BE",
	"57"  => "BF",
	"58"  => "BG",
	"59"  => "BH",
	"60"  => "BI",
	"61"  => "BJ",
	"62" => "BK",
	"63" => "BL",
	"64" => "BM",
	"65" => "BN",
	"66" => "BO",
	"67" => "BP",
	"68" => "BQ",
	"69" => "BR",
	"70" => "BS",
	"71" => "BT",
	"72" => "BU",
	);

	/**
	 * fpath:上传文件夹
	 * fname:保存文件名
	 * title:标题
	 * col:表头
	 * line:模板自动生成n行
	 * start：自x行读取
	 * check:检查哪列为空则跳过
	 * **/
	public $excel_ini = array(
	// 考勤
	"attend" => array(
	"fpath"  => "attend_in",
	"fname"  => "attend.xls",
	"title"  => "",
	"col"    => array("编号", "姓名", "迟到", "早退", "病假","事假","公假","婚假","产假","丧假","旷工", "备注"),
	"line"   => 6,
	"start"  => 3,
	"check"  => 1,
	"width"  => array(),
	"height" => 0,
	),
	"tteacher" => array(
	"fpath"  => "tteacher_in",
	"fname"  => "tteacher.xls",
	"title"  => "教师统计汇总表",
	"col"    => array("总人数", "男", "女", "研究生以上", "研究生","本科","专科","专科以下","高级","正高级","一级",
	"二级","三级","未定级","党员","预备党员", "非党员","聘用人数", "退休人数"),
	"line"   => 6,
	"start"  => 3,
	"check"  => 1,
	"width"  => array(),
	"height" => 0,
	),
	"cencount" => array(
	"fpath"  => "cencount_in",
	"fname"  => "cencount.xls",
	"title"  => "学生户籍统计汇总表",
	"col"    => array(),
	"line"   => 6,
	"start"  => 3,
	"check"  => 1,
	"width"  => array(),
	"height" => 0,
	),
	// 课程表
	"syllabus" => array(
	"fpath"  => "syllabus_in",
	"fname"  => "syllabus.xls",
	"title"  => "",
	"col"    => array("节次", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六"),
	"line"   => 6,
	"start"  => 3,
	"check"  => 1,
	"width"  => array("40"=> array("B","C","D","E","F","G","H")),
	"height" => "30",
	),
	// 周工作安排
	"workweek1" => array(
	"fpath"    => "workweek_in",
	"fname"    => "arrange.xls",
	"title"    => "",
	"col"      => array("周次", "日期", "主要内容", "负责部门", "备注",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H")),
	"height"   => "0",
	),
	// 每日工作安排
	"workweek" => array(
	"fpath"    => "workday_in",
	"fname"    => "arrange.xls",
	"title"    => "",
	"col"      => array("日期", "星期", "主要内容", "负责部门", "备注",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H")),
	"height"   => "0",
	),
	// 作息时间安排
	"rest" => array(
	"fpath"    => "rest_in",
	"fname"    => "arrange.xls",
	"title"    => "",
	"col"      => array("类别", "内容", "时间段", "备注",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H")),
	"height"   => "0",
	),
	// 计划安排
	"plan" => array(
	"fpath"    => "plan_in",
	"fname"    => "arrange.xls",
	"title"    => "",
	"col"      => array("星期", "日期", "主要内容", "负责部门", "备注",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H")),
	"height"   => "0",
	),
	// 公开课安排
	"open" => array(
	"fpath"    => "open_in",
	"fname"    => "open.xls",
	"title"    => "",
	"col"      => array("周次", "教师", "星期", "日期", "节次", "班级", "教学内容", "科目", "备注"),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H")),
	"height"   => "0",
	),
	// 值日表
	"duty" => array(
	"fpath"    => "duty_in",
	"fname"    => "duty.xls",
	"title"    => "",
	"col"      => array(),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
        // 值日表
        "temporary" => array(
            "fpath"    => "temporary_in",
            "fname"    => "temporary.xls",
            "title"    => "学生临时分班",
            "col"      => array('姓名',"性别"),
            "line"     => 6,
            "start"    => 3,
            "check"    => 1,
            "width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
            "height"   => "0",
        ),

	// 学生考勤表
	"check" => array(
	"fpath"    => "check_in",
	"fname"    => "check.xls",
	"title"    => "学生考勤表",
	"col"      => array( "学生编号", "学生姓名", "早退","迟到","病假","事假","逃课", "备注", ),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	"ttcher" => array(
	"fpath"    => "ttcher_in",
	"fname"    => "ttcher.xls",
	"title"    => "教师信息表",
	"col"      => array(),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	// 学生考勤表
	"first" => array(
	"fpath"    => "first_in",
	"fname"    => "first.xls",
	"title"    => "学生入学成绩",
	"col"      => array( "学生姓名", "全国学籍号","总分","语文","数学","英语", "物理","化学", "历史", "政治", "综合", "体育",  ),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),

	"noticeoutda" => array(
	"fpath"    => "noticeoutda_in",
	"fname"    => "noticeoutda.xls",
	"title"    => "通知审批表",
	"col"      => array("发布人", "审批人" ),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q")),
	"height"   => "0",
	),

	// 学生统计
	"studentcount" => array(
	"fpath"    => "studentcount_in",
	"fname"    => "studentcount.xls",
	"title"    => "",
	"col"      => array("姓名", "性别", "出生日期", "身份证件号", "民族","户口性质","联系方式",
	"学区","学号","全国学籍号","学籍变动","就读方式","残疾类型" ),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q")),
	"height"   => "0",
	),
	//教师统计
	"teachercount" => array(
	"fpath"    => "teacher_in",
	"fname"    => "teacher.xls",
	"title"    => "",
	"col"      => array("姓名", "性别", "出生日期", "学历", "职称","参加工作时间","到校工作时间",
	"在职状态" ),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U"
	,"V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ")),
	"height"   => "0",
	),
	//外聘
	"external" => array(
	"fpath"    => "external_in",
	"fname"    => "external.xls",
	"title"    => "",
	"col"      => array("姓名", "性别", "出生日期", "学历", "职称","参加工作时间","到校工作时间",
	"在职状态","员工性质" ),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	//外聘
	"exchange" => array(
	"fpath"    => "exchange_in",
	"fname"    => "exchange.xls",
	"title"    => "",
	"col"      => array("编号", "姓名", "变动事项", "日期", "原单位","目的单位","在岗信息",
	"备注", ),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	//人事变动1
	"appoint1" => array(
	"fpath"    => "appoint1_in",
	"fname"    => "appoint1.xls",
	"title"    => "部门任命表",
	"col"      => array("部门", "职务", "姓名",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	//人事变动2
	"appoint3" => array(
	"fpath"    => "appoint3_in",
	"fname"    => "appoint3.xls",
	"title"    => "级长任命表",
	"col"      => array("年级", "职务", "姓名",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	//人事变动3
	"appoint4" => array(
	"fpath"    => "appoint4_in",
	"fname"    => "appoint4.xls",
	"title"    => "班主任任命表",
	"col"      => array("年级","班级", "职务", "姓名",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	//人事变动4
	"appoint2" => array(
	"fpath"    => "appoint2_in",
	"fname"    => "appoint2.xls",
	"title"    => "教研组任命表",
	"col"      => array("教研组组长", "职务", "姓名",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	//人事变动5
	"appoint5" => array(
	"fpath"    => "appoint5_in",
	"fname"    => "appoint5.xls",
	"title"    => "备课组任命表",
	"col"      => array("学科组组长", "职务", "姓名",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	//人事变动6
	"appoint6" => array(
	"fpath"    => "appoint6_in",
	"fname"    => "appoint6.xls",
	"title"    => "学科组任命表",
	"col"      => array("学科组组长", "职务", "姓名",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	//分班
	"divide" => array(
	"fpath"    => "divide_in",
	"fname"    => "divide.xls",
	"title"    => "学生分班表",
	"col"      => array("班级名称", "学生姓名", "学籍号","座号",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "1",
	),
	// 职位任命表
	"appoint_stu" => array(
	"fpath"    => "appoint_stu_in",
	"fname"    => "appoint_stu.xls",
	"title"    => "职位任命表",
	"col"      => array("学号", "学生姓名", "学生职位",),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	// 毕业登记
	"graduate_in" => array(
	"fpath"    => "graduate_in",
	"fname"    => "graduate_in.xls",
	"title"    => "毕业登记表",
	"col"      => array(),
	"line"     => 6,
	"start"    => 3,
	"check"    => 1,
	"width"    => array("40"=> array("B","C","D","E","F","G","H","I","J","K","L","M","N")),
	"height"   => "0",
	),
	
	);

	public function __construct()
	{
		parent::__construct();
	}

	/** 导入 **/
	public function in($ini_name, $up = 1)
	{
		$data = array();

        $up_dir = UPLOAD_PATH."/{$this->excel_ini[$ini_name]['fpath']}";
        // 没有目录则创建目录
        $this->make_attach_dir($up_dir);

        $up_name = $this->excel_ini[$ini_name]['fname'];
        $up_path = "{$up_dir}/{$up_name}";

		if($up == '1'){
			//var_dump($_FILES);exit;
			// 保存上传文件到上传目录
			if (!empty($_FILES['in_excel']['tmp_name'])){
				copy($_FILES['in_excel']['tmp_name'], $up_path);
			}else{
				return false;
			}

		}

		// 读取excel
		include_once("lib/phpexcel/PHPExcel.php");
//        $extension = pathinfo($up_path,PATHINFO_EXTENSION);
//        if( $extension =='xlsx' )
//        {
//            $reader = PHPExcel_IOFactory::createReader('Excel2007');
//        }
//        else
//        {
//            $reader = PHPExcel_IOFactory::createReader('Excel5');
//        }
        try{
            $reader = PHPExcel_IOFactory::createReader('Excel5');
        }catch (Exception $e){
            $reader = PHPExcel_IOFactory::createReader('Excel2007');
        }

		//设置以Excel5格式(Excel97-2003工作簿)
		// 载入excel文件
		$PHPExcel = $reader->load($up_path);
		// 读取第一個工作表
		$sheet = $PHPExcel->getSheet(0);
		// 取得总行数
		$highestRow = $sheet->getHighestRow();
		// 取得总列数
		$highestColumm = $sheet->getHighestColumn();
		// 字母列转换为数字列 如:AA变为27
		$highestColumm = PHPExcel_Cell::columnIndexFromString($highestColumm);

		// 检查列数是否与表头相等
		if ($highestColumm != count($this->excel_ini[$ini_name]['col'])){
			//return false;
		}

		/** 循环读取每个单元格的数据 */
		for ($row = $this->excel_ini[$ini_name]['start']; $row <= $highestRow; $row++){
			$info = array();
			// 行数是以第1行开始
			for ($column = 0; $column < $highestColumm; $column++) {
				$k = $column+1;

				//列数是以第0列开始
				$info[$k] = $sheet->getCellByColumnAndRow($column, $row)->getValue();
				//$info[$k] = date("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($info[$k]) - 28800);

			}
			if (!empty($info)){
				$data[$row] = $info;
			}
		}

		// 处理后存入数据库
		foreach ($data as $key => $value){
			if ($data[$key][$this->excel_ini[$ini_name]['check']] == ""){
				unset($data[$key]);
			}
		}

		return $data;

	}



	// 导出模板
	public function outTp($ini_name)
	{

		if (empty($this->excel_ini[$ini_name]['fname'])){
			$fname = date("YmdHis").".xls";
		}else{
			$fname = $this->excel_ini[$ini_name]['fname'];
		}

		include_once("lib/phpexcel/PHPExcel.php");
		include_once("lib/phpexcel/PHPExcel/Writer/Excel2007.php");

		// 输出excel
		$objPHPExcel = new PHPExcel();

		$objPHPExcel->setActiveSheetIndex(0);
		// 输出标题
		$objPHPExcel->getActiveSheet()->mergeCells('A1:T1');
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->setCellValue("A1", $this->excel_ini[$ini_name]['title']);
		// 输出表头

		foreach ($this->excel_ini[$ini_name]['col'] as $key => $value){
			$objPHPExcel->getActiveSheet()->setCellValue($this->letter_ini[$key] . 2, $value);
		}

		// 输出空数据
		//		for ($i = $this->excel_ini[$ini_name]['start']; $i<= $this->excel_ini[$ini_name]['line']; $i++){
		//			foreach ($this->excel_ini[$ini_name]['col'] as $key => $value){
		//				$objPHPExcel->getActiveSheet()->setCellValue($this->letter_ini[$key] . $i, " ");
		//			}
		//		}

		$objPHPExcel->createSheet();

		//保存excel—2007格式
		//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
		//$objWriter->save("public/excel/xxx.xlsx");

		//直接输出到浏览器
		$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
		header("Pragma: public");
		header("Expires: 0");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Content-Type:application/force-download");
		header("Content-Type:application/vnd.ms-execl");
		header("Content-Type:application/octet-stream");
		header("Content-Type:application/download");;
		header("Content-Disposition:attachment;filename={$fname}");
		header("Content-Transfer-Encoding:binary");

		$objWriter->save('php://output');

		exit;

	}


	/**导出数据**/
	public function outData($ini_name, $data_lists)
	{


		if (empty($this->excel_ini[$ini_name]['fname'])){
			$fname = date("YmdHis").".xls";
		}else{
			$fname = $this->excel_ini[$ini_name]['fname'];
		}

		include_once("lib/phpexcel/PHPExcel.php");
		include_once("lib/phpexcel/PHPExcel/Writer/Excel2007.php");

		// 输出excel
		$objPHPExcel = new PHPExcel();
		$objPHPExcel->getProperties()->setTitle($this->excel_ini[$ini_name]['title']);

		// 设置单元格宽度
		if (!empty($this->excel_ini[$ini_name]['width'])){
			foreach ($this->excel_ini[$ini_name]['width'] as $key => $value){
				foreach ($value as $k => $v){
					$objPHPExcel->getActiveSheet()->getColumnDimension($v)->setWidth($key);
				}
			}
		}
		//var_dump($this->excel_ini[$ini_name]['col']);exit;

		// 输出标题
		$objPHPExcel->getActiveSheet()->mergeCells('A1:T1');
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
		$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->setCellValue("A1", $this->excel_ini[$ini_name]['title']);
		// 输出表头
		foreach ($this->excel_ini[$ini_name]['col'] as $key => $value){
			$poz = $this->letter_ini[$key].'2';
			//if($key<=25){
			$objPHPExcel->getActiveSheet()->setCellValue($poz, $value);
			//            }else{
			//                $objPHPExcel->getActiveSheet()->setCellValue('A'.$poz, $value);
			//            }

			//echo $this->letter_ini[$key]. 2 .$key;
			//exit;
		}

		// 输出数据
		$i = $this->excel_ini[$ini_name]['start'];
		foreach ((array)$data_lists as $key => $value){
			$value=array_values($value);//wq 11.19ttcher 导出BUG修复
			foreach ($value as $k => $v){
				//                if($k<=25){
				$pos =  $this->letter_ini[$k] . $i;
				$objPHPExcel->getActiveSheet()->setCellValue($pos, $v);
				//                }else{
				//
				//                }
			}
			if (!empty($this->excel_ini[$ini_name]['height'])){

				$objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight($this->excel_ini[$ini_name]['height']);
			}
			$i++;
		}

		$objPHPExcel->createSheet();

		//保存excel—2007格式
		//$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
		//$objWriter->save("public/excel/xxx.xlsx");

		//直接输出到浏览器
		$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
		header("Pragma: public");
		header("Expires: 0");
		header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
		header("Content-Type:application/force-download");
		header("Content-Type:application/vnd.ms-execl");
		header("Content-Type:application/octet-stream");
		header("Content-Type:application/download");;
		header("Content-Disposition:attachment;filename={$fname}");
		header("Content-Transfer-Encoding:binary");

		$objWriter->save('php://output');
		exit;


	}

	/** 递归创建目录 **/
	function make_attach_dir($path, $mode = 0777)
	{
		if (is_dir($path)) {
			if (!(is_writable($path))) {
				@chmod($path, $mode);
			}
			return true;
		} else {
			$oldmask = @umask(0);
			$partialpath = dirname($path);
			if (!self::make_attach_dir($partialpath, $mode)) {
				return false;
			} else {
				return mkdir($path, $mode);
			}
		}
	}
}



?>